package com.chenfan.filestore.utils.excel;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.IoUtils;
import com.alibaba.excel.util.StringUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


@Data
public class ImageHandler implements CellWriteHandler {

    public static int urlConnectTimeout = 1000;
    public static int urlReadTimeout = 5000;


    private String batchImageSize = "150x150";

    private String filePrefixPath;
    private boolean showPic = false;

    private String separator = "/";


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if(!showPic){
            return;
        }
        if ((head != null && head.getColumnIndex() != 1)|| isHead || cellDataList.isEmpty()) {
            return;
        }
        //能下载图片 则 下载 并清空单元格链接 防止vba重复加载
        //不能下载图片 则链接保存 直接用vba加载图片
        writeImage(cell,cellDataList);
//        CellWriteHandler.super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead);

    }

    private void writeImage(Cell cell, List<WriteCellData<?>> cellDataList) {
        Sheet sheet = cell.getSheet();
        String cellValueStr = String.valueOf(cellDataList.get(0).getStringValue());
        if(!isImage(cellValueStr)){
            return;
        }
        // cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个
        byte[] pic_file = getFile(getShortUrl(cellValueStr));
        if (pic_file == null || pic_file.length == 0) {
            return;
        }else{
            //直接将图片url置为空 如果小于1000张保留图片
            cell.setCellValue("");
        }
        int index = sheet.getWorkbook().addPicture(pic_file, HSSFWorkbook.PICTURE_TYPE_PNG);

        Drawing drawing = sheet.getDrawingPatriarch();
        if (drawing == null) {
            drawing = sheet.createDrawingPatriarch();
        }
        CreationHelper helper = sheet.getWorkbook().getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();
        // 设置图片坐标
        anchor.setDx1(0);
        anchor.setDx2(0);
        anchor.setDy1(0);
        anchor.setDy2(0);
        //设置图片位置
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 1);
        anchor.setRow1(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex() + 1);
        // 设置图片可以随着单元格移动
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        drawing.createPicture(anchor, index);
    }
    private byte[] getFile(String url) {
        InputStream inputStream = null;
        URLConnection urlConnection = null;
        try {
            urlConnection = new URL(getShortUrl(url)).openConnection();
            urlConnection.setConnectTimeout(urlConnectTimeout);
            urlConnection.setReadTimeout(urlReadTimeout);
            inputStream = urlConnection.getInputStream();
            return IoUtils.toByteArray(inputStream);
        }catch (Exception e){

        }finally {
            IOUtils.closeQuietly(inputStream);
            IOUtils.close(urlConnection);
        }
        return new byte[0];
    }



    public String getShortUrl(String url) {
        Pattern pattern = Pattern.compile("/\\d+x\\d+");
        Matcher matcher = pattern.matcher(url);
        if (matcher.find()) {
            url = url.replaceAll(matcher.group(), separator + this.batchImageSize);
        } else{
            url += separator + this.batchImageSize;
        }
        //不是url 则添加图片url
        if(!url.startsWith("http")){
            url = this.filePrefixPath + separator + url;
        }
        return url;
    }


    public boolean isImage(String imageUrl){
        if(StringUtils.isEmpty(imageUrl)){
            return false;
        }
//        Pattern pattern = Pattern.compile("\\d{4}/\\d{2}/\\d{2}/[0-9a-z]{32}.[a-z]{3,5}");
        Pattern pattern = Pattern.compile("[0-9a-z]{24}.[jpgJEPGpngPNGwebpWEBPbmpBMP]{3,5}");
        Matcher matcher = pattern.matcher(imageUrl);
        return matcher.find();
    }

}
